﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using com.eforceglobal.crux.BusinessObjects;
using com.eforceglobal.crux.DTOs;
using com.eforceglobal.crux.configuration;

namespace com.eforceglobal.crux.BusinessObjects.DatabaseQueries
{
    /// <summary>
    /// Centralized Class For ALL LINQ Queries Used In The Application. 
    /// </summary>
    public class Queries
    {
        CruxBusinessObjectsDataContext bc = new CruxBusinessObjectsDataContext
            (CruxConfigurationManager.GetCruxDBConnectionString());

        // -------------------------- Security Related ------------------------
        public User GetUser(string UserName, string Password, string CompanyName)
        {

            User user = bc.Users.SingleOrDefault(p => p.UserName == UserName &&
                                                 p.Password == Password &&
                                                 p.Company.CompanyName == CompanyName);

            return user;
        }
        public User GetUser(string UserName)
        {
            User user = bc.Users.SingleOrDefault(p => p.UserName == UserName);
            return user;
        }

        public User GetUser(string UserName, string Password)
        {
            User user = bc.Users.SingleOrDefault(p => p.UserName == UserName &&
                                                  p.Password == Password);
            return user;
        }


        public User GetUserFromCompany(string UserName, string CompanyName)
        {
            User user = bc.Users.SingleOrDefault(p => p.Company.CompanyName == CompanyName
                                                       && p.UserName == UserName);
            return user;
        }

        public User GetUserFromCompany(string UserName)
        {
            User user = bc.Users.SingleOrDefault(p => p.UserName == UserName);
            return user;
        }

        public IEnumerable<Role> GetRolesForUser(string UserName, string CompanyName)
        {
            IEnumerable<Role> rolesByUser = from roles in bc.Roles
                                            from userroles in bc.UserRoles
                                            from users in bc.Users
                                            where roles.RoleID == userroles.RoleID
                                            where users.UserID == userroles.UserID
                                            where users.UserName == UserName
                                            where users.Company.CompanyName == CompanyName
                                            select roles;
            return rolesByUser;
        }


        public IEnumerable<Role> GetRolesForUser(int UserID)
        {
            IEnumerable<Role> rolesByUser = from roles in bc.Roles
                                            from userroles in bc.UserRoles
                                            from users in bc.Users
                                            where roles.RoleID == userroles.RoleID
                                            where users.UserID == userroles.UserID
                                            where users.UserID == UserID
                                            select roles;
            return rolesByUser;

        }


        public IEnumerable<Role> GetRoles(string privilagename)
        {
            IEnumerable<Role> roles = from rolelist in bc.Roles
                                      from privilagelist in bc.Privilages
                                      from roleprivilagelist in bc.RolePrivilages
                                      where rolelist.RoleID == roleprivilagelist.RoleID
                                      where privilagelist.PrivilageID == roleprivilagelist.PrivilageID
                                      where privilagelist.PrivilageName == privilagename
                                      select rolelist;

            return roles;
        }


        public IEnumerable<Role> GetRoles(int companyid)
        {
            IEnumerable<Role> roles = from rolelist in bc.Roles
                                      from companylist in bc.Companies
                                      from rolesbycompanytypelist in bc.RolesByCompanyTypes
                                      where rolesbycompanytypelist.CompanyTypeID == companylist.CompanyTypeID
                                      where rolesbycompanytypelist.RoleID == rolelist.RoleID
                                      where companylist.CompanyID == companyid
                                      select rolelist;
            return roles;
        }

        
        public IEnumerable<Role> GetRolesNotHavingPrivilage(int companyid, string privilage)
        {
            IEnumerable<Role> roles = from rolelist in GetRoles(companyid)
                                          .Except(GetRoles(privilage))
                                      select rolelist;


            return roles;
        }




        public IEnumerable<UserRole> GetUserRolesForUser(int UserID, int Companyid)
        {
            
            IEnumerable<UserRole> userRolesByUser = from roles in bc.Roles
                                                    from userroles in bc.UserRoles
                                                    from rolesbycompanytype in bc.RolesByCompanyTypes
                                                    from companies in bc.Companies
                                                    from users in bc.Users
                                                    where roles.RoleID == userroles.RoleID
                                                    where users.UserID == userroles.UserID
                                                    where users.UserID == UserID
                                                    where roles.RoleID == rolesbycompanytype.RoleID
                                                    where companies.CompanyTypeID == rolesbycompanytype.CompanyTypeID
                                                    where rolesbycompanytype.CompanyTypeID == companies.CompanyTypeID
                                                    where companies.CompanyID == Companyid
                                                    select userroles;
            return userRolesByUser;
        }

        public IEnumerable<CompanyRepresentative> GetCompanyRepresentativeForUser(int RepresentativeID)
        {
            IEnumerable<CompanyRepresentative> companyRepresentative =
                                             from allcompanies in bc.Companies
                                             from allcompanyrepresentatives in bc.CompanyRepresentatives
                                             where allcompanies.CompanyID == allcompanyrepresentatives.CompanyID
                                             where allcompanyrepresentatives.UserID == RepresentativeID
                                             select allcompanyrepresentatives;
            return companyRepresentative;
        }

        public CompanyInformation GetCompanyForUser(string UserName)
        {
            User user = GetUser(UserName);
            CompanyInformation companyInformation =
                ClassConverter.CopyCompanyToCompanyInformation(user.Company);
            return companyInformation;
        }

        // ------------------------ Locations Related -------------------------
        public IEnumerable<Location> GetLocations(int CompanyID)
        {
            IEnumerable<Location> locations = from locationlist in bc.Locations
                                              where locationlist.CompanyID == CompanyID
                                              select locationlist;

            return locations;
        }

        public IEnumerable<Location> GetAllLocations()
        {
             IEnumerable < Location > locations = from locationsList in bc.Locations
                                                        select locationsList;
             return locations;
        }

        public Location GetLocation(int LocationID)
        {
            Location location = bc.Locations.Single(p => p.LocationID == LocationID);
            return location;
        }

        public Location GetLocation(string LocationName, string CompanyName)
        {
            Location location = bc.Locations.SingleOrDefault(p => p.LocationName == LocationName
                                                      && p.Company.CompanyName == CompanyName);
            return location;
        }

        public void InsertLocation(Location location)
        {
            bc.Locations.InsertOnSubmit(location);
            bc.SubmitChanges();
        }

        public void SubmitChanges()
        {
            bc.SubmitChanges();
        }

        public void DeleteLocation(Location location)
        {
            bc.Locations.DeleteOnSubmit(location);
            bc.SubmitChanges();
        }


        // ------- Sub Location Related ------

        public IEnumerable<SubLocation> GetSubLocations(int LocationID)
        {
            IEnumerable<SubLocation> subLocations = from subLocationList in bc.SubLocations
                                                    where subLocationList.LocationID == LocationID
                                                    select subLocationList;
            return subLocations;

        }

        public SubLocation GetSubLocation(int SubLocationID)
        {
            SubLocation subLocation = bc.SubLocations.Single(p => p.SubLocationID == SubLocationID);
            return subLocation;
        }

        public void InserSubLocation(SubLocation subLocation)
        {
            bc.SubLocations.InsertOnSubmit(subLocation);
            bc.SubmitChanges();
        }

        public void DeleteSubLocation(SubLocation subLocation)
        {
            bc.SubLocations.DeleteOnSubmit(subLocation);
            bc.SubmitChanges();
        }

        // ----------------- Company Realted --------------------------

        public IEnumerable<Company> GetCompanies()
        {
            IEnumerable<Company> companies = from companyList in bc.Companies
                                             select companyList;
            return companies;
        }


        public IEnumerable<Company> GetCompaniesByType(string CompanyType)
        {
            IEnumerable<Company> companies = from allcompanies in bc.Companies
                                             where allcompanies.CompanyType.CompanyTypeName == CompanyType
                                             select allcompanies;
            return companies;
        }

        public Company GetCompany(int CompanyID)
        {
            Company company = bc.Companies.Single(p => p.CompanyID == CompanyID);
            return company;
        }

        public Company GetCompany(string CompanyName)
        {
            Company company = bc.Companies.SingleOrDefault(p => p.CompanyName == CompanyName);
            return company;
        }

        public void DeleteCompany(Company company, bool ImmediateSubmit)
        {
            bc.Companies.DeleteOnSubmit(company);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        public void InsertCompany(Company company)
        {
            bc.Companies.InsertOnSubmit(company);
            bc.SubmitChanges();
        }

        // ------ Department Related
        public IEnumerable<Department> GetDepartments(int CompanyID)
        {
            IEnumerable<Department> departments = from departmentslist in bc.Departments
                                                  where departmentslist.CompanyID == CompanyID
                                                  select departmentslist;

            return departments;
        }

        public IEnumerable<Department> GetDepartmentsForUser(int UserID)
        {
            IEnumerable<Department> departments = from departmentslist in bc.Departments
                                                  from userdepartments in bc.UserDepartments
                                                  where departmentslist.DepartmentID == userdepartments.DepartmentID
                                                  where userdepartments.UserID == UserID
                                                  select departmentslist;

            return departments;
        }

        public Department GetDepartment(int DepartmentID)
        {
            Department depertment = bc.Departments.Single(p => p.DepartmentID == DepartmentID);
            return depertment;
        }

        public Department GetDepartment(string CompanyName, string DepartmentName)
        {
            Department department = bc.Departments.SingleOrDefault(p => p.DepartmentName == DepartmentName
                                                                     && p.Company.CompanyName == CompanyName);
            return department;

        }


        public void InsertDepartment(Department department)
        {
            bc.Departments.InsertOnSubmit(department);
            bc.SubmitChanges();
        }

        public void DeleteDepartment(Department department)
        {
            bc.Departments.DeleteOnSubmit(department);
            bc.SubmitChanges();
        }

        public void DeleteDepartments(IEnumerable<Department> departments, bool ImmediateSubmit)
        {
            bc.Departments.DeleteAllOnSubmit(departments);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        //----------------- User Related ------------------------------

        public IEnumerable<User> GetUsers(int CompanyID)
        {
            IEnumerable<User> users = from userlist in GetAllUsers()
                                      from usercompanies in bc.UserCompanies
                                      where userlist.UserID == usercompanies.UserID
                                      where usercompanies.CompanyID == CompanyID
                                      select userlist;

            return users;
        }

        public IEnumerable<User> GetAllUsers()
        {
            IEnumerable<User> users = from userlist in bc.Users
                                      select userlist;
            return users;
        }

        public User GetUser(int UserID)
        {
            User user = GetAllUsers().Single(p => p.UserID == UserID);
            return user;
        }

        public void InsertUser(User user)
        {
            bc.Users.InsertOnSubmit(user);
            bc.SubmitChanges();
        }

        public void DeleteUser(User user)
        {
            bc.Users.DeleteOnSubmit(user);
            bc.SubmitChanges();
        }


        public void AssignRolesToUser(UserRole userRole, bool ImmediateSubmit)
        {
            bc.UserRoles.InsertOnSubmit(userRole);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }


        public void AssignRepresentativeToCompany(CompanyRepresentative companyRepresentative, bool ImmediateSubmit)
        {
            bc.CompanyRepresentatives.InsertOnSubmit(companyRepresentative);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        public void AssignRepresentativeToLocation(LocationRepresentative locationRepresentative, bool ImmediateSubmit)
        {
            bc.LocationRepresentatives.InsertOnSubmit(locationRepresentative);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }


        public void RemoveUserFromRole(UserRole userRole, bool ImmediateSubmit)
        {
            bc.UserRoles.DeleteOnSubmit(userRole);
            if (ImmediateSubmit)
                bc.SubmitChanges();

        }

        public void RemoveCompanyRepresentative(CompanyRepresentative companyRepresentative, bool ImmediateSubmit)
        {
            bc.CompanyRepresentatives.DeleteOnSubmit(companyRepresentative);
            if (ImmediateSubmit)
                bc.SubmitChanges();

        }

        public void RemoveLocationRepresentative(LocationRepresentative locationRepresentative, bool ImmediateSubmit)
        {
            bc.LocationRepresentatives.DeleteOnSubmit(locationRepresentative);
            if (ImmediateSubmit)
                bc.SubmitChanges();

        }


        public IEnumerable<UserDepartment> GetUserDepartmentsForUser(int UserID, int CompanyID)
        {
            IEnumerable<UserDepartment> userDepartmentsByUser = from departments in bc.Departments
                                                                from userdepartments in bc.UserDepartments
                                                                from users in bc.Users
                                                                where departments.DepartmentID == userdepartments.DepartmentID
                                                                where departments.CompanyID == CompanyID
                                                                where users.UserID == userdepartments.UserID
                                                                where users.UserID == UserID
                                                                select userdepartments;
            return userDepartmentsByUser;

        }

        public void RemoveUserFromDepartment(UserDepartment userDepartment, bool ImmediateSubmit)
        {
            bc.UserDepartments.DeleteOnSubmit(userDepartment);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        public void AssignDepartmentToUser(UserDepartment userDepartment, bool ImmediateSubmit)
        {
            bc.UserDepartments.InsertOnSubmit(userDepartment);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        // ------------ Company Type Related ----------------------
        public IEnumerable<CompanyType> GetAllCompanyTypes()
        {
            IEnumerable<CompanyType> companytypes = from companytypeslist in bc.CompanyTypes
                                                    select companytypeslist;
            return companytypes;
        }


        public SubLocation GetSubLocation(int LocationID, string SubLocationName)
        {
            SubLocation subLocation = bc.SubLocations
                .SingleOrDefault(p => p.LocationID == LocationID && p.SubLocationName == SubLocationName);
            return subLocation;
        }

        public IEnumerable<Location> GetLocationsByCompanyType(string CompanyType)
        {
            //TODO: Don't Hard Code InternalCompany. 
            IEnumerable<Location> locations = from alllocations in bc.Locations
                                              where alllocations.Company.CompanyType.CompanyTypeName == CompanyType
                                              select alllocations;
            return locations;
        }

        public IEnumerable<Company> GetCompaniesRepresentedBy(int RepresentativeID)
        {
            IEnumerable<Company> companies = from allcompanies in bc.Companies
                                             from allcompanyrepresentatives in bc.CompanyRepresentatives
                                             where allcompanies.CompanyID == allcompanyrepresentatives.CompanyID
                                             where allcompanyrepresentatives.UserID == RepresentativeID
                                             select allcompanies;
            return companies;
        }

        public IEnumerable<Location> GetLocationsRepresentedBy(int RepresentativeID)
        {
            IEnumerable<Location> locations = from allLocations in bc.Locations
                                              from allLocationRepresentatives in bc.LocationRepresentatives
                                              where allLocations.LocationID == allLocationRepresentatives.LocationID
                                              where allLocationRepresentatives.UserID == RepresentativeID
                                              select allLocations;
            return locations;
        }

        public IEnumerable<LocationRepresentative> GetLocationRepresentativeForUser(int RepresentativeID)
        {
            IEnumerable<LocationRepresentative> locationRepresentative =
                                             from alllocations in bc.Locations
                                             from alllocationrepresenatatives in bc.LocationRepresentatives
                                             where alllocations.LocationID == alllocationrepresenatatives.LocationID
                                             where alllocationrepresenatatives.UserID == RepresentativeID
                                             select alllocationrepresenatatives;
            return locationRepresentative;
        }

        public IEnumerable<User> GetUsersInRoles(List<string> names)
        {
            IEnumerable<User> usersinroles =
                from userroles in bc.UserRoles
                from users in bc.Users
                from roles in bc.Roles
                .Where(p => names.Contains(p.RoleName))
                where userroles.UserID == users.UserID
                where userroles.RoleID == roles.RoleID
                select users;
            return usersinroles;
        }

        //----------------------------------Roles Related------------------------------------

        public void InsertRole(Role role, bool SubmitChanges)
        {
            bc.Roles.InsertOnSubmit(role);
            if (SubmitChanges)
                bc.SubmitChanges();
        }

        public IEnumerable<Role> GetAllRoles()
        {
            IEnumerable<Role> roles = from roleList in bc.Roles
                                      select roleList;
            return roles;
        }

        public IEnumerable<Privilage> GetAllPrivilages()
        {
            IEnumerable<Privilage> privilages = from privilageList in bc.Privilages
                                                select privilageList;
            return privilages;
        }

        public Role GetRole(int RoleID)
        {
            Role role = bc.Roles.Single(p => p.RoleID == RoleID);
            return role;
        }

        public Role GetRole(string RoleName)
        {
            Role role = bc.Roles.SingleOrDefault(p => p.RoleName == RoleName);
            return role;
        }

        public void DeleteRole(Role role)
        {
            bc.Roles.DeleteOnSubmit(role);
            bc.SubmitChanges();
        }

        
        public IEnumerable<Privilage> GetPrivilagesForRole(int RoleID)
        {
            IEnumerable<Privilage> privilagesByRole = from roles in bc.Roles
                                                      from rolePrivilages in bc.RolePrivilages
                                                      from privilages in bc.Privilages
                                                      where roles.RoleID == rolePrivilages.RoleID
                                                      where privilages.PrivilageID == rolePrivilages.PrivilageID
                                                      where roles.RoleID == RoleID
                                                      select privilages;
            return privilagesByRole;

        }


        public IEnumerable<CompanyType> GetCompanyTypesForRole(int RoleID)
        {
            IEnumerable<CompanyType> compamyTypesByRole = from roles in bc.Roles
                                                          from rolesByCompanyTypes in bc.RolesByCompanyTypes
                                                          from companyTypes in bc.CompanyTypes
                                                          where roles.RoleID == rolesByCompanyTypes.RoleID
                                                          where companyTypes.CompanyTypeID == rolesByCompanyTypes.CompanyTypeID
                                                          where roles.RoleID == RoleID
                                                          select companyTypes;
            return compamyTypesByRole;
        }

        public void RemovePrivilagesFromRole(RolePrivilage rolePrivilage, bool SubmitChanges)
        {
            bc.RolePrivilages.DeleteOnSubmit(rolePrivilage);
            if (SubmitChanges)
                bc.SubmitChanges();
        }

        public void RemoveCompanyTypesFromRole(RolesByCompanyType rolesByCompanyType, bool SubmitChanges)
        {
            bc.RolesByCompanyTypes.DeleteOnSubmit(rolesByCompanyType);
            if (SubmitChanges)
                bc.SubmitChanges();
        }

        public IEnumerable<Role> GetRolesByCompanyType(string CompanyType)
        {
            IEnumerable<Role> roles = from allroles in bc.Roles
                                      from allrolesbycompanytype in bc.RolesByCompanyTypes
                                      from allcompanytypes in bc.CompanyTypes
                                      where allroles.RoleID == allrolesbycompanytype.RoleID
                                      where allcompanytypes.CompanyTypeID == allrolesbycompanytype.CompanyTypeID
                                      where allcompanytypes.CompanyTypeName.ToLower() == CompanyType.ToLower()
                                      select allroles;
            return roles;
        }

        public IEnumerable<CompanyInformation> GetCompaniesForUser(int userid)
        {
            IEnumerable<CompanyInformation> companies = from allcompanies in bc.Companies
                                             from allusers in bc.Users
                                             from allusercompanies in bc.UserCompanies
                                             where allusers.UserID == allusercompanies.UserID
                                             where allcompanies.CompanyID == allusercompanies.CompanyID
                                             where allusers.UserID == userid
                                             select ClassConverter.CopyCompanyToCompanyInformation(allcompanies);
            List<CompanyInformation> listofcompanies = companies.ToList();
            return companies;

                

        }

        public void DeleteCompanyAssociationForUser(UserCompany usercompany, bool immediateSubmit)
        {
            bc.UserCompanies.DeleteOnSubmit(usercompany);
            if (immediateSubmit)
                bc.SubmitChanges();
        }

        public IEnumerable<UserCompany> GetUserCompanyForUser(int userid)
        {
            IEnumerable<UserCompany> listofusercompany = from allusercompanies in bc.UserCompanies
                                                   where allusercompanies.UserID == userid
                                                   select allusercompanies;
            return listofusercompany;
        }

        public void AssignUserToCompany(UserCompany usercompany, bool ImmediateSubmit)
        {
            bc.UserCompanies.InsertOnSubmit(usercompany);
            if (ImmediateSubmit)
                bc.SubmitChanges();
        }

        public IEnumerable<SecurityQuestion> GetAllSecurityQuestions()
        {
            IEnumerable<SecurityQuestion> questions = from ques in bc.SecurityQuestions
                                                      select ques;
            return questions;
        }


        public void InsertPassword(ChangedPassword changedPassword)
        {
            bc.ChangedPasswords.InsertOnSubmit(changedPassword);
        }

        public IEnumerable<ChangedPassword> GetChangedPasswordByUserID(int userID)
        {
            IEnumerable<ChangedPassword> changedPasswords = from password in bc.ChangedPasswords
                                                            where password.UserID == userID
                                                            select password;
            return changedPasswords;
        }

        public void DeleteSavedPassword(List<ChangedPassword> passwordList)
        {
            bc.ChangedPasswords.DeleteAllOnSubmit(passwordList);
        }
    }
}
